﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace PmfWeb.Core.Data
{
    ///<summary>
    /// 数据库连接访问类
    ///</summary>
    public class DBCOM
    {
        /// <summary>
        /// 
        /// </summary>
        private string _isConnectionString;

        /// <summary>
        /// 当前数据库链接字符串
        /// </summary>
        private readonly string _curConnectionString = System.Configuration.ConfigurationManager.AppSettings["conn"] ?? "";
        /// <summary>
        /// 应用程序是否控制连接（默认为“否”，系统自动生成连接 ）
        /// </summary>
        private bool _isConnInd = false;

        /// <summary>
        /// 应用程序是否控制事务（默认为“是”，系统不自动生成事务 但应用程序加事务必须控制连接）
        /// </summary>
        private bool _isTranInd = true; 

        /// <summary>
        /// 判断事务是否已开始
        /// </summary>
        private bool _isTranBegin = false;

        /// <summary>
        /// 数据库连接超时时间 执行超时 (单位：秒)
        /// </summary>
        private int _connectTimeOut = 60;

        /// <summary>
        /// DB连接串数组
        /// </summary>
        private string[] _connectString = new string[10];

        /// <summary>
        /// 数据库连接对象
        /// </summary>
        private SqlConnection _currentConnection;

        /// <summary>
        /// 事务对象
        /// </summary>
        private SqlTransaction _currentTran;

        /// <summary>
        /// 参数数组
        /// </summary>
        private ArrayList _paramArray = new ArrayList();

        /// <summary>
        /// 保存存储过程参数结构
        /// </summary>
        public struct StruParam
        {
            ///<summary>
            /// 参数名
            ///</summary>
            public string Name;

            ///<summary>
            /// 参数类型
            ///</summary>
            public SqlDbType DataType;

            ///<summary>
            /// 参数长度
            ///</summary>
            public int Length;

            ///<summary>
            /// 参数方向
            ///</summary>
            public ParameterDirection Direction;

            ///<summary>
            /// 参数值
            ///</summary>
            public string Value;
        }

        ///<summary>
        /// 构造器
        ///</summary>
        public DBCOM()
        {
            if(_curConnectionString.Length > 0)
            {
                _connectString[0] = _curConnectionString;
            }
            else
            {
                _connectString[0] = @"server=rain\SQL2008;Database=grape;User Id=sa;Password=20031999;connection reset=false;connection lifetime=5;min pool size=1;max pool size=3";
            }
        }

        ///<summary>
        /// 构造器
        ///</summary>
        ///<param name="connectArray">数据库连接字符串数组</param>
        public DBCOM(string[] connectArray)
        {
            _connectString = connectArray;
        }

        /// <summary>
        /// 连接串属性
        /// </summary>
        public string ConnectionString
        {
            get { return _isConnectionString; }
            set { _isConnectionString = value; }
        }

        /// <summary>
        /// 应用程序是否控制连接（默认为“否”，系统自动生成连接 ）

        /// </summary>
        public bool IsConnInd
        {
            get { return _isConnInd; }
            set { _isConnInd = value; }
        }

        /// <summary>
        /// 应用程序是否控制事务（默认为“是”，系统不自动生成事务）
        /// </summary>
        public bool IsTranInd
        {
            get { return _isTranInd; }
            set { _isTranInd = value; }
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="errText">错误信息</param>
        /// <param name="iConnectID">数据库编号</param>
        /// <returns>成功返回1 失败小于0</returns>
        public int Open(ref string errText, int iConnectID)
        {
            string connectString;
            connectString = _connectString[iConnectID];
            if (0 == connectString.Length)
            {
                errText = "SQL连接串为空";
                return -190010001; //SQL连接串为空
            }
            try
            {
                _currentConnection = new SqlConnection(connectString);
                _currentConnection.Open();
            }
            catch (Exception ex)
            {
                errText = "数据库操作错误@@@@Open：" + iConnectID.ToString() + ex.Message;
                return -190010002;
            }
            return 1;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="errText">错误信息</param>
        /// <returns>成功返回1 失败小于0</returns>
        public int Close(ref string errText)
        {
            if (_currentConnection == null)
            {
                errText = "数据库连接未初始化";
                return -190010003; //数据库连接未初始化		
            }
            if (_currentConnection.State != ConnectionState.Open)
            {
                errText = "数据库连接未打开";
                return -190010004; //数据库连接未打开
            }
            try
            {
                _currentConnection.Close();
            }
            catch (Exception ex)
            {
                errText = "数据库操作错误@@@@Close：" + ex.Message;
                return -190010002;
            }
            return 1;
        }

        /// <summary>
        /// 开始事务
        /// </summary>
        /// <param name="errText">错误信息</param>
        /// <returns>成功返回1 失败小于0</returns>
        public int BeginTran(ref string errText)
        {
            if (_currentConnection == null)
            {
                errText = "数据库连接未初始化";
                return -190010003; //数据库连接未初始化		
            }
            if (_currentConnection.State != ConnectionState.Open)
            {
                errText = "数据库连接未打开";
                return -190010004; //数据库连接未打开
            }

            if (_isTranBegin)
            {
                errText = "数据库已开始事务";
                return -190010009;
            }
            try
            {
                _currentTran = _currentConnection.BeginTransaction();
            }
            catch (Exception ex)
            {
                errText = "数据库操作错误@@@@BeginTran：" + ex.Message;
                return -190010002;
            }
            _isTranBegin = true;
            return 1;
        }

        /// <summary>
        /// 事务回滚
        /// </summary>
        /// <param name="errText">错误信息</param>
        /// <returns>成功返回1 失败小于0</returns>
        public int RollbackTran(ref string errText)
        {
            if (_currentConnection == null)
            {
                errText = "数据库连接未初始化";
                return -190010003; //数据库连接未初始化		
            }
            if (_currentConnection.State != ConnectionState.Open)
            {
                errText = "数据库连接未打开";
                return -190010004; //数据库连接未打开
            }

            if (_isTranBegin == false)
            {
                errText = "数据库未开始事务";
                return -190010010;
            }
            try
            {
                _currentTran.Rollback();
            }
            catch (Exception ex)
            {
                errText = "数据库操作错误@@@@RollbackTran：" + ex.Message;
                return -190010002;
            }
            _isTranBegin = false;
            return 1;
        }

        /// <summary>
        /// 事务提交
        /// </summary>
        /// <param name="errText"></param>
        /// <returns>成功返回1 失败小于0</returns>
        public int CommitTran(ref string errText)
        {
            if (_currentConnection == null)
            {
                errText = "数据库连接未初始化";
                return -190010003; //数据库连接未初始化		
            }
            if (_currentConnection.State != ConnectionState.Open)
            {
                errText = "数据库连接未打开";
                return -190010004; //数据库连接未打开
            }

            if (_isTranBegin == false)
            {
                errText = "数据库未开始事务";
                return -190010010;
            }

            try
            {
                _currentTran.Commit();
            }
            catch (Exception ex)
            {
                errText = "数据库操作错误@@@@RollbackTran：" + ex.Message;
                return -190010002;
            }
            _isTranBegin = false;
            return 1;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="errText">错误信息</param>
        /// <param name="iConnectId">数据库编号</param>
        /// <param name="sql">SQL语句</param>
        /// <returns>成功返回1 失败小于0</returns>
        public int RunSql(ref string errText, int iConnectId, string sql)
        {
            SqlCommand cmd = new SqlCommand();
            int iReturn;

            if (sql == "")
            {
                errText = "SQL语句为空";
                return -190010005; //SQL语句为空
            }

            if (_isConnInd == false) //不控制连接
            {
                iReturn = Open(ref errText, iConnectId);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }

            if (_currentConnection.State != ConnectionState.Open)
            {
                errText = "数据库连接未打开";
                return -190010004; //数据库连接未打开
            }

            if ((_isTranInd == false) && (_isTranBegin == false)) //不控制事务
            {
                iReturn = BeginTran(ref errText);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }
            if ((_isTranBegin) && (_currentTran == null))
            {
                errText = "数据库事务未开始";
                return -190010011;
            }

            try
            {
                cmd.Connection = _currentConnection;
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = _connectTimeOut;
                if (_currentTran != null) cmd.Transaction = _currentTran;

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                if (_isTranInd == false) //不控制事务
                {
                    iReturn = RollbackTran(ref errText); //对于系统自动回滚，不影响正常的返回，所以不处理其返回

                }
                if (_isConnInd == false) //不控制连接
                {
                    iReturn = Close(ref errText);
                }
                errText = "数据库操作错误@@@@RunSql：" + ex.Message + "@@@@" + sql;
                return -190010002;
            }
            cmd.Dispose();

            if (!_isTranInd) //不控制事务
            {
                iReturn = CommitTran(ref errText);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }

            if (!_isConnInd) //不控制连接
            {
                iReturn = Close(ref errText);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }

            return 1;
        }

        /// <summary>
        /// 执行SQL语句返回结果集
        /// </summary>
        /// <param name="errText">错误信息</param>
        /// <param name="iconnect">数据库编号</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="outds">返回结果集</param>
        /// <returns>成功返回1 失败小于0</returns>
        public int RunSQLData(ref string errText, int iconnect, string sql, ref DataSet outds)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand();
            int iReturn;

            if (sql == "")
            {
                errText = "SQL语句为空";
                return -190010005; //SQL语句为空
            }

            if (_isConnInd == false) //不控制连接
            {
                iReturn = Open(ref errText, iconnect);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }

            if (_currentConnection.State != ConnectionState.Open)
            {
                errText = "数据库连接未打开";
                return -190010004; //数据库连接未打开
            }

            if ((_isTranInd == false) && (_isTranBegin == false)) //不控制事务
            {
                iReturn = BeginTran(ref errText);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }
            if ((_isTranBegin) && (_currentTran == null))
            {
                errText = "数据库事务未开始";
                return -190010011;
            }

            try
            {
                cmd.Connection = _currentConnection;
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = _connectTimeOut;
                if (_currentTran != null) cmd.Transaction = _currentTran;

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                if (_isTranInd == false) //不控制事务
                {
                    iReturn = RollbackTran(ref errText); //对于系统自动回滚，不影响正常的返回，所以不处理其返回
                }
                if (_isConnInd == false) //不控制连接
                {
                    iReturn = Close(ref errText);
                }
                errText = "数据库操作错误@@@@RunSQLData：" + ex.Message + "@@@@" + sql;
                return -190010002;
            }
            cmd.Dispose();

            if (_isTranInd == false) //不控制事务
            {
                iReturn = CommitTran(ref errText);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }

            if (_isConnInd == false) //不控制连接
            {
                iReturn = Close(ref errText);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }
            outds = ds;
            return 1;
        }


        /// <summary>
        /// 执行SQL语句返回结果集的第一个字段
        /// </summary>
        /// <param name="errText">错误信息</param>
        /// <param name="iConnectId">数据库编号</param>
        /// <param name="sql">SQL语句</param>
        /// <returns>成功返回1 失败小于0</returns>
        public int ExecuteScalar(ref string errText, int iConnectId, string sql, ref string outColumn)
        {
            SqlCommand cmd = new SqlCommand();
            int iReturn;

            if (sql == "")
            {
                errText = "SQL语句为空";
                return -190010005; //SQL语句为空
            }

            if (!_isConnInd) //不控制连接
            {
                iReturn = Open(ref errText, iConnectId);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }

            if (_currentConnection.State != ConnectionState.Open)
            {
                errText = "数据库连接未打开";
                return -190010004; //数据库连接未打开
            }

            if ((_isTranInd == false) && (_isTranBegin == false)) //不控制事务
            {
                iReturn = BeginTran(ref errText);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }
            if ((_isTranBegin) && (_currentTran == null))
            {
                errText = "数据库事务未开始";
                return -190010011;
            }

            try
            {
                cmd.Connection = _currentConnection;
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = _connectTimeOut;
                if (_currentTran != null) cmd.Transaction = _currentTran;

                outColumn = cmd.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                if (_isTranInd == false) //不控制事务
                {
                    iReturn = RollbackTran(ref errText); //对于系统自动回滚，不影响正常的返回，所以不处理其返回
                }
                if (_isConnInd == false) //不控制连接
                {
                    iReturn = Close(ref errText);
                }
                errText = "数据库操作错误@@@@RunSQLData：" + ex.Message + "@@@@" + sql;
                return -190010002;
            }
            cmd.Dispose();

            if (_isTranInd == false) //不控制事务
            {
                iReturn = CommitTran(ref errText);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }

            if (_isConnInd == false) //不控制连接
            {
                iReturn = Close(ref errText);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }

            return 1;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="errText">错误信息</param>
        /// <param name="iConnectId">数据库编号</param>
        /// <param name="spName">存储过程名称</param>
        /// <returns>成功返回1 失败小于0</returns>
        public int RunSP(ref string errText, int iConnectId, string spName)
        {
            int iReturn, iParamsCount, li_ParamNum;
            SqlCommand cmd = new SqlCommand();
            SqlParameter parameter;
            StruParam lstru_Param;

            if (spName == "")
            {
                errText = "存储过程名称为空";
                return -190010006; //存储过程名称为空
            }

            if (_isConnInd == false) //不控制连接
            {
                iReturn = Open(ref errText, iConnectId);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }

            if (_currentConnection.State != ConnectionState.Open)
            {
                errText = "数据库连接未打开";
                return -190010004;     //数据库连接未打开
            }

            if ((_isTranInd == false) && (_isTranBegin == false)) //不控制事务
            {
                iReturn = BeginTran(ref errText);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }
            if ((_isTranBegin == true) && (_currentTran == null))
            {
                errText = "数据库事务未开始";
                return -190010011;
            }

            try
            {
                cmd.Connection = _currentConnection;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = spName;
                cmd.CommandTimeout = _connectTimeOut;
                if (_currentTran != null) cmd.Transaction = _currentTran;

                if (_paramArray.Count > 0)
                {
                    iParamsCount = _paramArray.Count;
                    for (li_ParamNum = 0; li_ParamNum < iParamsCount; li_ParamNum++)
                    {
                        lstru_Param = (StruParam) _paramArray[li_ParamNum];
                        parameter = new SqlParameter();
                        parameter = cmd.Parameters.Add(lstru_Param.Name, lstru_Param.DataType, lstru_Param.Length);
                        parameter.Direction = lstru_Param.Direction;
                        parameter.Value = lstru_Param.Value;
                        /*
                        if (lstru_Param.Direction == ParameterDirection.Output)
                        {
                            lparam_SQL.Direction = lstru_Param.Direction;
                            lparam_SQL.Value = lstru_Param.Value;
                        }
                        else
                        {
                            lparam_SQL.Value = lstru_Param.Value;
                        }
                        */
                    }
                }

                cmd.ExecuteNonQuery();

                //取返回参数

                if (_paramArray.Count > 0)
                {
                    iParamsCount = _paramArray.Count;
                    for (li_ParamNum = iParamsCount - 1; li_ParamNum >= 0; li_ParamNum--)
                    {
                        lstru_Param = (StruParam) _paramArray[li_ParamNum];
                        if (lstru_Param.Direction == ParameterDirection.Output)
                        {
                            lstru_Param.Value = cmd.Parameters[lstru_Param.Name].Value.ToString();
                            _paramArray.RemoveAt(li_ParamNum);
                            _paramArray.Add(lstru_Param);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                if (_isTranInd == false) //不控制事务
                {
                    iReturn = RollbackTran(ref errText); //对于系统自动回滚，不影响正常的返回，所以不处理其返回

                }
                if (_isConnInd == false) //不控制连接
                {
                    iReturn = Close(ref errText);
                }
                errText = "数据库操作错误@@@@RunSP：" + ex.Message + "@@@@" + spName;
                return -190010002;
            }
            cmd.Dispose();

            if (_isTranInd == false) //不控制事务
            {
                iReturn = CommitTran(ref errText);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }

            if (_isConnInd == false) //不控制连接
            {
                iReturn = Close(ref errText);
                if (iReturn < 0)
                {
                    return iReturn;
                }
            }

            return 1;
        }

        /// <summary>
        /// 执行存储过程返回结果集
        /// </summary>
        /// <param name="as_ErrText">错误信息</param>
        /// <param name="ai_ConnectID">数据库编号</param>
        /// <param name="as_SPName">存储过程名称</param>
        /// <param name="ads_out_Data">返回结果集</param>
        /// <returns>成功返回1 失败小于0</returns>
        public int RunSPData(ref string as_ErrText, int ai_ConnectID, string as_SPName, ref DataSet ads_out_Data)
        {
            int li_Return, li_ParamCount, li_ParamNum;
            SqlCommand lcmd_SQL = new SqlCommand();
            DataSet lds_Data = new DataSet();
            SqlParameter lparam_SQL;
            StruParam lstru_Param;

            if (as_SPName == "")
            {
                as_ErrText = "存储过程名称为空";
                return -190010006; //存储过程名称为空
            }

            if (_isConnInd == false) //不控制连接

            {
                li_Return = Open(ref as_ErrText, ai_ConnectID);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }

            if (_currentConnection.State != ConnectionState.Open)
            {
                as_ErrText = "数据库连接未打开";
                return -190010004; //数据库连接未打开
            }

            if ((_isTranInd == false) && (_isTranBegin == false)) //不控制事务
            {
                li_Return = BeginTran(ref as_ErrText);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }
            if ((_isTranBegin == true) && (_currentTran == null))
            {
                as_ErrText = "数据库事务未开始";
                return -190010011;
            }

            try
            {
                lcmd_SQL.Connection = _currentConnection;
                lcmd_SQL.CommandType = CommandType.StoredProcedure;
                lcmd_SQL.CommandText = as_SPName;
                lcmd_SQL.CommandTimeout = _connectTimeOut;
                if (_currentTran != null) lcmd_SQL.Transaction = _currentTran;

                if (_paramArray.Count > 0)
                {
                    li_ParamCount = _paramArray.Count;
                    for (li_ParamNum = 0; li_ParamNum < li_ParamCount; li_ParamNum++)
                    {
                        lstru_Param = (StruParam) _paramArray[li_ParamNum];
                        lparam_SQL = new SqlParameter();
                        lparam_SQL = lcmd_SQL.Parameters.Add(lstru_Param.Name, lstru_Param.DataType, lstru_Param.Length);
                        if (lstru_Param.Direction == ParameterDirection.Output )
                        {
                            lparam_SQL.Direction = lstru_Param.Direction;
                        }
                        else if(lstru_Param.Direction == ParameterDirection.InputOutput)
                        {
                            lparam_SQL.Direction = lstru_Param.Direction;
                            lparam_SQL.Value = lstru_Param.Value;
                        }
                        else
                        {
                            lparam_SQL.Value = lstru_Param.Value;
                        }
                    }
                }

                SqlDataAdapter ladapt_Operation = new SqlDataAdapter(lcmd_SQL);
                ladapt_Operation.Fill(lds_Data);

                //取返回参数

                if (_paramArray.Count > 0)
                {
                    li_ParamCount = _paramArray.Count;
                    for (li_ParamNum = li_ParamCount - 1; li_ParamNum >= 0; li_ParamNum--)
                    {
                        lstru_Param = (StruParam) _paramArray[li_ParamNum];
                        if (lstru_Param.Direction == ParameterDirection.Output || lstru_Param.Direction == ParameterDirection.InputOutput)
                        {
                            lstru_Param.Value = lcmd_SQL.Parameters[lstru_Param.Name].Value.ToString();
                            _paramArray.RemoveAt(li_ParamNum);
                            _paramArray.Add(lstru_Param);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                if (_isTranInd == false) //不控制事务
                {
                    li_Return = RollbackTran(ref as_ErrText); //对于系统自动回滚，不影响正常的返回，所以不处理其返回

                }
                if (_isConnInd == false) //不控制连接
                {
                    li_Return = Close(ref as_ErrText);
                }
                as_ErrText = "数据库操作错误@@@@RunSPData：" + ex.Message + "@@@@" + as_SPName;
                return -190010002;
            }
            lcmd_SQL.Dispose();

            if (_isTranInd == false) //不控制事务
            {
                li_Return = CommitTran(ref as_ErrText);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }

            if (_isConnInd == false) //不控制连接
            {
                li_Return = Close(ref as_ErrText);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }

            ads_out_Data = lds_Data;
            return 1;
        }

        /// <summary>
        /// 添加存储过程参数
        /// </summary>
        /// <param name="as_ErrText">错误信息</param>
        /// <param name="as_ParamName">参数名</param>
        /// <param name="adt_DataType">数据类型</param>
        /// <param name="apd_Direction">输入输出类型</param>
        /// <param name="ai_DataLength">参数长度</param>
        /// <param name="as_ParamValue">参数值</param>
        /// <returns>成功返回1 失败小于0</returns>
        public int AddParam(ref string as_ErrText, string as_ParamName, SqlDbType adt_DataType, ParameterDirection apd_Direction, int ai_DataLength, string as_ParamValue)
        {
            if (as_ParamName == "")
            {
                as_ErrText = "存储过程参数名称为空";
                return -190010007; //存储过程参数名称为空		
            }
            StruParam lstru_Param;
            lstru_Param.Name = as_ParamName;
            lstru_Param.DataType = adt_DataType;
            lstru_Param.Direction = apd_Direction;
            lstru_Param.Length = ai_DataLength;
            lstru_Param.Value = as_ParamValue;
            _paramArray.Add(lstru_Param);
            as_ErrText = "";
            return 1;
        }

        /// <summary>
        /// 清空原有的存储过程参数定义
        /// </summary>
        /// <param name="as_ErrText">错误信息</param>
        /// <returns>成功返回1 失败小于0</returns>
        public int ClearSqlParams(ref string as_ErrText)
        {
            int li_ParamCount;
            li_ParamCount = _paramArray.Count;
            if (li_ParamCount > 0)
            {
                _paramArray.RemoveRange(0, li_ParamCount);
            }

            as_ErrText = "";
            return 1;
        }

        /// <summary>
        /// 得到存储过程参数值
        /// </summary>
        /// <param name="as_ErrText">错误信息</param>
        /// <param name="as_ParamName">参数名</param>
        /// <param name="as_ParamValue">参数值</param>
        /// <returns>成功返回1 失败小于0</returns>
        public int GetParam(ref string as_ErrText, string as_ParamName, ref string as_ParamValue)
        {
            int li_ParamCount, li_ParamNum;
            StruParam lstru_Param;

            li_ParamCount = _paramArray.Count;
            if (li_ParamCount < 1)
            {
                as_ErrText = "存储过程参数名称为空";
                return -190010007; //存储过程参数名称为空
            }
            for (li_ParamNum = 0; li_ParamNum < li_ParamCount; li_ParamNum++)
            {
                lstru_Param = (StruParam) _paramArray[li_ParamNum];
                if (lstru_Param.Name == as_ParamName)
                {
                    as_ParamValue = lstru_Param.Value;
                    as_ErrText = "";
                    return 1;
                }
            }
            as_ErrText = "存储过程没有指定参数";
            return -190010008; //存储过程没有指定参数
        }

        /// <summary>
        /// 得到所有存储过程参数值（暂时）
        /// </summary>
        /// <param name="as_ErrText">错误信息</param>
        /// <param name="as_Value">参数值（以@@@@分隔参数，以&&&&分隔参数名和参数值）</param>
        /// <returns>成功返回1 失败小于0</returns>
        public int GetAllParam(ref string as_ErrText, ref string as_Value)
        {
            int li_ParamCount, li_ParamNum;
            StruParam lstru_Param;
            as_Value = "";

            li_ParamCount = _paramArray.Count;
            if (li_ParamCount < 1)
            {
                as_ErrText = "存储过程参数名称为空";
                return -190010007; //存储过程参数名称为空
            }
            for (li_ParamNum = 0; li_ParamNum < li_ParamCount; li_ParamNum++)
            {
                lstru_Param = (StruParam) _paramArray[li_ParamNum];
                as_Value = as_Value + lstru_Param.Name + "&&&&" + lstru_Param.Value + "@@@@";
            }
            as_ErrText = "";
            return 1;
        }


        public int RunSQLDataWithPara(ref string as_ErrText, int ai_ConnectID, string as_SQL, ref DataSet ads_out_Data)
        {
            int li_Return, li_ParamCount, li_ParamNum;
            SqlCommand lcmd_SQL = new SqlCommand();
            DataSet lds_Data = new DataSet();
            SqlParameter lparam_SQL;
            StruParam lstru_Param;

            if (as_SQL == "")
            {
                as_ErrText = "SQL语句为空";
                return -190010005; //SQL语句为空
            }

            if (_isConnInd == false) //不控制连接
            {
                li_Return = Open(ref as_ErrText, ai_ConnectID);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }

            if (_currentConnection.State != ConnectionState.Open)
            {
                as_ErrText = "数据库连接未打开";
                return -190010004; //数据库连接未打开
            }

            if ((_isTranInd == false) && (_isTranBegin == false)) //不控制事务
            {
                li_Return = BeginTran(ref as_ErrText);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }
            if ((_isTranBegin == true) && (_currentTran == null))
            {
                as_ErrText = "数据库事务未开始";
                return -190010011;
            }


            try
            {
                lcmd_SQL.Connection = _currentConnection;
                lcmd_SQL.CommandText = as_SQL;
                lcmd_SQL.CommandType = CommandType.Text;
                lcmd_SQL.CommandTimeout = _connectTimeOut;
                if (_currentTran != null) lcmd_SQL.Transaction = _currentTran;

                if (_paramArray.Count > 0)
                {
                    li_ParamCount = _paramArray.Count;
                    for (li_ParamNum = 0; li_ParamNum < li_ParamCount; li_ParamNum++)
                    {
                        lstru_Param = (StruParam) _paramArray[li_ParamNum];
                        lparam_SQL = new SqlParameter();
                        lparam_SQL = lcmd_SQL.Parameters.Add(lstru_Param.Name, lstru_Param.DataType, lstru_Param.Length);
                        if (lstru_Param.Direction == ParameterDirection.Output)
                        {
                            lparam_SQL.Direction = lstru_Param.Direction;
                        }
                        else
                        {
                            lparam_SQL.Value = lstru_Param.Value;
                        }
                    }
                }

                SqlDataAdapter ladapt_Operation = new SqlDataAdapter(lcmd_SQL);
                ladapt_Operation.Fill(lds_Data);

                //取返回参数

                if (_paramArray.Count > 0)
                {
                    li_ParamCount = _paramArray.Count;
                    for (li_ParamNum = li_ParamCount - 1; li_ParamNum >= 0; li_ParamNum--)
                    {
                        lstru_Param = (StruParam) _paramArray[li_ParamNum];
                        if (lstru_Param.Direction == ParameterDirection.Output)
                        {
                            lstru_Param.Value = lcmd_SQL.Parameters[lstru_Param.Name].Value.ToString();
                            _paramArray.RemoveAt(li_ParamNum);
                            _paramArray.Add(lstru_Param);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                if (_isTranInd == false) //不控制事务
                {
                    li_Return = RollbackTran(ref as_ErrText); //对于系统自动回滚，不影响正常的返回，所以不处理其返回
                }
                if (_isConnInd == false) //不控制连接
                {
                    li_Return = Close(ref as_ErrText);
                }
                as_ErrText = "数据库操作错误@@@@RunSPData：" + ex.Message + "@@@@" + as_SQL;
                return -190010002;
            }
            lcmd_SQL.Dispose();

            if (_isTranInd == false) //不控制事务
            {
                li_Return = CommitTran(ref as_ErrText);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }

            if (_isConnInd == false) //不控制连接
            {
                li_Return = Close(ref as_ErrText);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }

            ads_out_Data = lds_Data;
            return 1;
        }

        #region Add by liyd TestCase需要

        /// <summary>
        /// 数据集内容更新到数据库中
        /// </summary>
        /// <param name="as_ErrText">错误信息</param>
        /// <param name="ai_ConnectID">数据库编号</param>
        /// <returns>成功返回1 失败小于0</returns>
        public int UpdateDataSetToDB(ref string as_ErrText, int ai_ConnectID, string as_TableName, DataSet ads_Data)
        {
            int li_Return;
            SqlCommand lcmd_SQL = new SqlCommand();
            DataSet lds_Data = new DataSet();

            if (as_TableName == "")
            {
                as_ErrText = "表名为空";
                return -190010005; //SQL语句为空
            }

            if (_isConnInd == false) //不控制连接
            {
                li_Return = Open(ref as_ErrText, ai_ConnectID);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }

            if (_currentConnection.State != ConnectionState.Open)
            {
                as_ErrText = "数据库连接未打开";
                return -190010004; //数据库连接未打开
            }

            if ((_isTranInd == false) && (_isTranBegin == false)) //不控制事务
            {
                li_Return = BeginTran(ref as_ErrText);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }
            if ((_isTranBegin == true) && (_currentTran == null))
            {
                as_ErrText = "数据库事务未开始";
                return -190010011;
            }

            try
            {
                lcmd_SQL.Connection = _currentConnection;
                if (_currentTran != null) lcmd_SQL.Transaction = _currentTran;
                lcmd_SQL.CommandType = CommandType.Text;
                lcmd_SQL.CommandTimeout = _connectTimeOut;

                //清空数据
                lcmd_SQL.CommandText = "DELETE FROM " + as_TableName;
                lcmd_SQL.ExecuteNonQuery();

                //取得列信息，不需要数据

                lcmd_SQL.CommandText = "SELECT * FROM " + as_TableName;

                SqlDataAdapter ladpt_Operation = new SqlDataAdapter(lcmd_SQL);
                ladpt_Operation.Fill(lds_Data, as_TableName);

                ConvertExcelDSToDesDS(ads_Data, ref lds_Data);

                //SqlDataAdapter ladpt_Operation = new SqlDataAdapter("SELECT TOP 0 * FROM " + as_TableName, _currentConnection);
                SqlCommandBuilder builder = new SqlCommandBuilder(ladpt_Operation); //
                ladpt_Operation.Update(lds_Data, as_TableName);
            }
            catch (Exception ex)
            {
                if (_isTranInd == false) //不控制事务
                {
                    li_Return = RollbackTran(ref as_ErrText); //对于系统自动回滚，不影响正常的返回，所以不处理其返回

                }
                if (_isConnInd == false) //不控制连接
                {
                    li_Return = Close(ref as_ErrText);
                }
                as_ErrText = "数据库操作错误@@@@UpdateDataSetToDB：" + ex.Message + "@@@@" + as_TableName;
                return -190010002;
            }

            if (_isTranInd == false) //不控制事务
            {
                li_Return = CommitTran(ref as_ErrText);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }

            if (_isConnInd == false) //不控制连接
            {
                li_Return = Close(ref as_ErrText);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }
            return 1;
        }

        /// <summary>
        /// 将Excel中的数据转换成数据库对应的DataSet对象
        /// </summary>
        /// <returns>DataSet类型对象</returns>
        public void ConvertExcelDSToDesDS(DataSet dsExcel, ref DataSet dsDes)
        {
            if (dsExcel != null && dsExcel.Tables.Count > 0)
            {
                foreach (DataRow dr in dsExcel.Tables[0].Rows)
                {
                    DataRow drTemp = dsDes.Tables[0].NewRow();
                    foreach (DataColumn dc in dsDes.Tables[0].Columns)
                    {
                        try
                        {
                            if (dr[dc.ColumnName] != null && dr[dc.ColumnName] != DBNull.Value &&
                                dr[dc.ColumnName].ToString().ToLower() != "[ignore]")
                            {
                                drTemp[dc.ColumnName] = Convert.ChangeType(dr[dc.ColumnName], dc.DataType);
                            }
                        }
                        catch (System.ArgumentException e)
                        {
                        }
                    }

                    dsDes.Tables[0].Rows.Add(drTemp);
                }
            }
        }

        #endregion

        #region Add by liyd 增加批处理

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="as_ErrText">错误信息</param>
        /// <param name="ai_ConnectID">数据库编号</param>
        /// <returns>成功返回1 失败小于0</returns>
        public int RunBatchSQL(ref string as_ErrText, int ai_ConnectID, ArrayList arr_SQL)
        {
            SqlCommand lcmd_SQL = new SqlCommand();
            int li_Return;

            if (arr_SQL.Count == 0)
            {
                as_ErrText = "SQL语句为空";
                return -190010005; //SQL语句为空
            }

            if (_isConnInd == false) //不控制连接
            {
                li_Return = Open(ref as_ErrText, ai_ConnectID);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }

            if (_currentConnection.State != ConnectionState.Open)
            {
                as_ErrText = "数据库连接未打开";
                return -190010004; //数据库连接未打开
            }

            if ((_isTranInd == false) && (_isTranBegin == false)) //不控制事务
            {
                li_Return = BeginTran(ref as_ErrText);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }
            if ((_isTranBegin == true) && (_currentTran == null))
            {
                as_ErrText = "数据库事务未开始";
                return -190010011;
            }

            try
            {
                lcmd_SQL.Connection = _currentConnection;
                lcmd_SQL.CommandType = CommandType.Text;
                lcmd_SQL.CommandTimeout = _connectTimeOut;
                if (_currentTran != null) lcmd_SQL.Transaction = _currentTran;

                foreach (string as_SQL in arr_SQL)
                {
                    lcmd_SQL.CommandText = as_SQL;
                    lcmd_SQL.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                if (_isTranInd == false) //不控制事务
                {
                    li_Return = RollbackTran(ref as_ErrText); //对于系统自动回滚，不影响正常的返回，所以不处理其返回

                }
                if (_isConnInd == false) //不控制连接

                {
                    li_Return = Close(ref as_ErrText);
                }
                as_ErrText = "数据库操作错误@@@@RunSql：" + ex.Message + "@@@@" + arr_SQL.ToString();
                return -190010002;
            }
            lcmd_SQL.Dispose();

            if (_isTranInd == false) //不控制事务
            {
                li_Return = CommitTran(ref as_ErrText);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }

            if (_isConnInd == false) //不控制连接
            {
                li_Return = Close(ref as_ErrText);
                if (li_Return < 0)
                {
                    return li_Return;
                }
            }

            return 1;
        }
        #endregion Add end
    }
}